package db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import util.ExecutionTime;
import beans.Star;
import db.connection.ConnectionPool;

public class StarsDAO {

	private static StarsDAO starsDAO = null;
	private ConnectionPool connectionPool = null;
	private Connection conn = null;

	public static synchronized StarsDAO getIstance()
			throws Exception {
		if (starsDAO == null) {
			starsDAO = new StarsDAO();
		}
		return starsDAO;
	}

	private StarsDAO() throws Exception {
		connectionPool = ConnectionPool.getIstance();
	}

	public ArrayList<Star> getStarsAll() throws Exception {
		conn=connectionPool.getConnection();
		ArrayList<Star> res = new ArrayList<Star>();
		Statement statement = this.conn.createStatement();
		ResultSet resultSet = statement.executeQuery("select * from star");
		while (resultSet.next()) {
			res.add((Star) BeanFactory.getObj(new Star().getClass().getName(),
					resultSet));
		}
		this.connectionPool.releaseConnection(this.conn);

		return res;
	}

	public void recordStars(ArrayList<Star> starList) {
		try {
			conn=connectionPool.getConnection();
			String sql = "insert into star values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			PreparedStatement pst = conn.prepareStatement(sql);
			int chunkSize =1000;
			ExecutionTime et = new ExecutionTime();
			et.start();
			int parts = starList.size()/chunkSize;
			for(int i=0;i<parts;i++){
				for(int j=0;j<chunkSize;j++){
					int index=i*chunkSize+j;
					if(index+1>starList.size())
							continue;
					Star s = starList.get(index);
					pst.setInt(1, s.getStarID());
					pst.setInt(2, s.getHip());
					pst.setInt(3 ,s.getHd());
					pst.setInt(4 ,s.getHr());
					pst.setString(5 ,s.getGliese());
					pst.setString(6 , s.getBayerFlamsteed());
					pst.setString(7 ,s.getProperName());
					pst.setDouble(8 ,s.getRa());
					pst.setDouble(9 ,s.getDecl());
					pst.setDouble(10 ,s.getDistance());
					pst.setDouble(11 ,s.getMag());
					pst.setDouble(12 ,s.getAbsMag());
					pst.setString(13 ,s.getSpectrum());
					pst.setDouble(14 ,s.getColorIndex());
					pst.setDouble(15 ,s.getX());
					pst.setDouble(16 ,s.getY());
					pst.setDouble(17 ,s.getZ());
					pst.addBatch();	
				}
				pst.executeBatch();
				System.out.println("intert:"+(i+1)*chunkSize+"/"+starList.size() + " Expected end time: " +et.getExpectedTime((i+1)*chunkSize, starList.size()));
				
			}
			
		} 
			catch (Exception e) 
		{
			e.printStackTrace();
	
		}

		 
	}
	

	public void recordStarsLast(ArrayList<Star> starList) {
		try {
			conn=connectionPool.getConnection();
			String sql = "insert into star values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			PreparedStatement pst = conn.prepareStatement(sql);
			int chunkSize =1000;
			ExecutionTime et = new ExecutionTime();
			et.start();
			int parts = starList.size()/chunkSize;
			for(int i=87475;i<874700;i++){
					int index=i;
					if(index+1>starList.size())
							continue;
					Star s = starList.get(index);
					pst.setInt(1, s.getStarID());
					pst.setInt(2, s.getHip());
					pst.setInt(3 ,s.getHd());
					pst.setInt(4 ,s.getHr());
					pst.setString(5 ,s.getGliese());
					pst.setString(6 , s.getBayerFlamsteed());
					pst.setString(7 ,s.getProperName());
					pst.setDouble(8 ,s.getRa());
					pst.setDouble(9 ,s.getDecl());
					pst.setDouble(10 ,s.getDistance());
					pst.setDouble(11 ,s.getMag());
					pst.setDouble(12 ,s.getAbsMag());
					pst.setString(13 ,s.getSpectrum());
					pst.setDouble(14 ,s.getColorIndex());
					pst.setDouble(15 ,s.getX());
					pst.setDouble(16 ,s.getY());
					pst.setDouble(17 ,s.getZ());
					pst.addBatch();	
				}
				pst.executeBatch();
				//System.out.println("intert:"+(i+1)*chunkSize+"/"+starList.size() + " Expected end time: " +et.getExpectedTime((i+1)*chunkSize, starList.size()));
				
			
		} 
			catch (Exception e) 
		{
			e.printStackTrace();
	
		}

		 
	}

	public ArrayList<Star> getStarsByDistance(int distance) throws Exception {
		conn=connectionPool.getConnection();
		ArrayList<Star> res = new ArrayList<Star>();
		Statement statement = this.conn.createStatement();
		ResultSet resultSet = statement.executeQuery("select * from star where distance<"+distance);
		while (resultSet.next()) {
			res.add((Star) BeanFactory.getObj(new Star().getClass().getName(),
					resultSet));
		}
		this.connectionPool.releaseConnection(this.conn);

		return res;
	}

	
}
